﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data;

namespace JsonSubmitForm.App_Code
{
    public class EmployeeData : DataObject
    {
        public List<Entity.Employee> GetAllEmployee()
        {
            SqlDataReader drEmployee = GetDataReader("sproc_GetEmployee");
            List<Entity.Employee> Employees = new List<Entity.Employee>();
            Entity.Employee objEmployee;

            while (drEmployee.Read())
            {
                objEmployee = new Entity.Employee();

                objEmployee.Id = Convert.ToInt32(drEmployee["Id"].ToString());
                objEmployee.FirstName = drEmployee["FirstName"].ToString();
                objEmployee.MiddleName = drEmployee["MiddleName"].ToString();
                objEmployee.LastName = drEmployee["LastName"].ToString();
                objEmployee.Title = Convert.ToInt32(drEmployee["Title"].ToString());
                objEmployee.Position = drEmployee["Position"].ToString();
                objEmployee.JoinedDate = DateTime.Parse(drEmployee["JoinedDate"].ToString());
                objEmployee.EmployeeID = drEmployee["EmpID"].ToString();

                Employees.Add(objEmployee);
            }
            drEmployee.Close();

            return Employees;
        }

        public List<string> GetEmployeeID()
        {
            SqlDataReader drEmployee = GetDataReader("sproc_GetEmployeeID");
            List<string> idList = new List<string>();

            while (drEmployee.Read())
            {
                idList.Add(drEmployee["EmpID"].ToString());
            }

            drEmployee.Close();

            return idList;
        }

        public byte[] GetPhoto(int id)
        {
            SqlParameter[] mParams = {
                                new SqlParameter("@eid", SqlDbType.Int)
                            };

            mParams[0].Value = id;

            SqlDataReader drEmployee = GetDataReader("sproc_GetPhoto", mParams);
            byte[] img = new byte[1];

            while (drEmployee.Read())
            {
                if (drEmployee["Photo"].ToString().Length > 0)
                    img = (byte[])drEmployee["Photo"];
            }

            return img;
        }

        public Entity.Employee GetEmployeeByID(int id)
        {
            SqlParameter[] mParams =	{
                                new SqlParameter("@id",SqlDbType.Int)
                            };
            
            mParams[0].Value = id;
            // execute stored procedure
            SqlDataReader drEmployee = GetDataReader("sproc_GetEmployeeByID", mParams);
            Entity.Employee objEmployee = new Entity.Employee();

            if (drEmployee.Read())
            {
                objEmployee.Id = Convert.ToInt32(drEmployee["Id"].ToString());
                objEmployee.FirstName = drEmployee["FirstName"].ToString();
                objEmployee.MiddleName = drEmployee["MiddleName"].ToString();
                objEmployee.LastName = drEmployee["LastName"].ToString();
                objEmployee.Title = Convert.ToInt32(drEmployee["Title"].ToString());
                objEmployee.Position = drEmployee["Position"].ToString();
                objEmployee.JoinedDate = DateTime.Parse(drEmployee["JoinedDate"].ToString());
                objEmployee.EmployeeID = drEmployee["EmpID"].ToString();

            }

            drEmployee.Close();

            return objEmployee;
        }

        public Entity.Status InsertEmployee(Entity.Employee vobjEmployee)
        {
            SqlParameter[] mParams =	{
                                new SqlParameter("@firstname",SqlDbType.VarChar,50),
                                new SqlParameter("@middlename",SqlDbType.VarChar,50),
                                new SqlParameter("@lastname",SqlDbType.VarChar,50),
                                new SqlParameter("@title",SqlDbType.Int),
                                new SqlParameter("@position",SqlDbType.VarChar,50),
                                new SqlParameter("@joineddate",SqlDbType.DateTime),
                                new SqlParameter("@empid", SqlDbType.VarChar, 50),
                                new SqlParameter("@successFlag", SqlDbType.Bit),
                                new SqlParameter("@message", SqlDbType.VarChar, 200)
                            };

            mParams[0].Value = vobjEmployee.FirstName;
            mParams[1].Value = vobjEmployee.MiddleName;
            mParams[2].Value = vobjEmployee.LastName;
            mParams[3].Value = vobjEmployee.Title;
            mParams[4].Value = vobjEmployee.Position;
            mParams[5].Value = vobjEmployee.JoinedDate;
            mParams[6].Value = vobjEmployee.EmployeeID;

            mParams[7].Direction = ParameterDirection.Output;
            mParams[8].Direction = ParameterDirection.Output;

            // execute stored procedure
            RunProcedure("sproc_CreateEmployee", mParams);

            Entity.Status stat = new Entity.Status();
            stat.flag = (bool)mParams[7].Value;
            stat.message = (string)mParams[8].Value;

            return stat;
        }

        public Entity.Status UploadPhoto(string id, byte[] pic)
        {
            SqlParameter[] mParams = {

                                         new SqlParameter("@id", SqlDbType.VarChar, 50),
                                         new SqlParameter("@photo", SqlDbType.Image),
                                         new SqlParameter("@successFlag", SqlDbType.Bit),
                                         new SqlParameter("@message", SqlDbType.VarChar, 200)

                                     };

            mParams[0].Value = id;
            mParams[1].Value = pic;
            mParams[2].Direction = ParameterDirection.Output;
            mParams[3].Direction = ParameterDirection.Output;

            // execute stored procedure
            RunProcedure("sproc_UploadPhoto", mParams);

            Entity.Status stat = new Entity.Status();
            stat.flag = (bool)mParams[2].Value;
            stat.message = (string)mParams[3].Value;

            return stat;
        }

        public Entity.Status UpdateEmployee(Entity.Employee vobjEmployee)
        {
            SqlParameter[] mParams =	{
                                
                                new SqlParameter("@id",SqlDbType.Int),
                                new SqlParameter("@firstname",SqlDbType.VarChar,50),
                                new SqlParameter("@middlename",SqlDbType.VarChar,50),
                                new SqlParameter("@lastname",SqlDbType.VarChar,50),
                                new SqlParameter("@title",SqlDbType.Int),
                                new SqlParameter("@position",SqlDbType.VarChar,50),
                                new SqlParameter("@joineddate",SqlDbType.DateTime),
                                new SqlParameter("@empid", SqlDbType.VarChar, 50),
                                new SqlParameter("@successFlag", SqlDbType.Bit),
                                new SqlParameter("@message", SqlDbType.VarChar, 200)

							};

            mParams[0].Value = vobjEmployee.Id;
            mParams[1].Value = vobjEmployee.FirstName;
            mParams[2].Value = vobjEmployee.MiddleName;
            mParams[3].Value = vobjEmployee.LastName;
            mParams[4].Value = vobjEmployee.Title;
            mParams[5].Value = vobjEmployee.Position;
            mParams[6].Value = vobjEmployee.JoinedDate;
            mParams[7].Value = vobjEmployee.EmployeeID;

            mParams[8].Direction = ParameterDirection.Output;
            mParams[9].Direction = ParameterDirection.Output;

            // execute stored procedure
            RunProcedure("sproc_UpdateEmployee", mParams);

            Entity.Status stat = new Entity.Status();
            stat.flag = (bool)mParams[8].Value;
            stat.message = (string)mParams[9].Value;

            return stat;
        }

        public Entity.Status DeleteEmployee(int vobjEmployeeID)
        {
            SqlParameter[] mParams =	{
                                new SqlParameter("@id",SqlDbType.Int),
                                new SqlParameter("@successFlag", SqlDbType.Bit),
                                new SqlParameter("@message", SqlDbType.VarChar, 200)
                            };

            mParams[0].Value = vobjEmployeeID;
            mParams[1].Direction = ParameterDirection.Output;
            mParams[2].Direction = ParameterDirection.Output;

            // execute stored procedure
            RunProcedure("sproc_DeleteEmployee", mParams);

            Entity.Status stat = new Entity.Status();
            stat.flag = (bool)mParams[1].Value;
            stat.message = (string)mParams[2].Value;

            return stat;
        }
    }
}